package com.jimmy.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import javax.sql.DataSource;

import com.jimmy.dao.pk8583logdetail;
import com.jimmy.dao.upbillrouter;
import com.jimmy.pool.myConnection;

public class billrouterBean {
	DataSource ds;
	Connection con;
	PreparedStatement ps;

	ResultSet rset = null;

	public Collection getbillrouter() {
		String getbillSql = "select commerceCode,remark from upbillrouter order by remark";
		Collection result = new java.util.ArrayList();
		try {
			con = myConnection.getDataSource().getConnection();
			ps = con.prepareStatement(getbillSql);
			rset = ps.executeQuery();
			while (rset.next()) {
				String remark = rset.getString("remark");
				String commerceCode = rset.getString("commerceCode");
				result.add(new upbillrouter(commerceCode, remark));
			}
			return result;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public String getCount(String startDate, String stopDate, String code,
			String success) {
		String getbillSql = "select count(*) from pk8583logdetail where region20 is not null ";
		if (!code.equals("")) {
			getbillSql += " and  region42 = '" + code + "' ";
		}
		if (!startDate.equals("")) {
			if (getbillSql.indexOf("where") != -1) {
				getbillSql += " and dealDT between '" + startDate + " 00:00:00' "
						+ " and '" + stopDate + " 23:59:59' ";
			} else {
				getbillSql += " where dealDT between '" + startDate + " 00:00:00' "
						+ " and '" + stopDate + " 23:59:59' ";
			}
		}
		if (success.equals("true")) {
			if (getbillSql.indexOf("where") != -1) {
				getbillSql += " and region39 = '00' ";
			} else {
				getbillSql += " where region39 = '00' ";
			}
		}
		try {
			con = myConnection.getDataSource().getConnection();
			ps = con.prepareStatement(getbillSql);
			rset = ps.executeQuery();
			rset.next();
			return rset.getString(1);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public Collection getLogInfo(String startDate, String stopDate,
			String code, int page, String success) {
		String str = page * 30 + "";
		String getbillSql = "select * from pk8583logdetail where region20 is not null ";
		if (!code.equals("")) {
			getbillSql += " and  region42 = '" + code + "' ";
		}
		if (!startDate.equals("")) {
			if (getbillSql.indexOf("where") != -1) {
				getbillSql += " and dealDT between '" + startDate + " 00:00:00' "
						+ " and '" + stopDate + " 23:59:59' ";
			} else {
				getbillSql += " where dealDT between '" + startDate + " 00:00:00' "
						+ " and '" + stopDate + " 23:59:59' ";
			}
		}
		if (success.equals("true")) {
			if (getbillSql.indexOf("where") != -1) {
				getbillSql += " and region39 = '00' ";
			} else {
				getbillSql += " where region39 = '00' ";
			}
		}
		getbillSql += " order by dealDT desc ";
		if (page != -1) {
			getbillSql += " limit " + str + " ,30 ";
		}
		System.out.println("sql " + getbillSql);
		Collection result = new java.util.ArrayList();
		try {
			con = myConnection.getDataSource().getConnection();
			ps = con.prepareStatement(getbillSql);
			rset = ps.executeQuery();
			while (rset.next()) {
				String id = rset.getString("id");
				String region39 = rset.getString("region39");
				String region42 = rset.getString("region42");
				String region11 = rset.getString("region11");
				String region4 = rset.getString("region4");
				String region2 = rset.getString("region2");
				if (region4 == null) {
					region4 = "";
				}
				if (!region4.equals("")) {
					region4 = getTranamt(region4);
				}
				String region7 = rset.getString("region7");
				region7 = rset.getString("dealDT").substring(0,4) + "-" + region7.substring(0, 2) + "-"
						+ region7.substring(2, 4) + " "
						+ region7.substring(4, 6) + ":"
						+ region7.substring(6, 8) + ":"
						+ region7.substring(8, 10);
				String region20 = rset.getString("region20");
				String region21 = rset.getString("terminalNoArea")
						+ rset.getString("terminalNo");
				result
						.add(new pk8583logdetail(id, region39, region42,
								region11, region2, region4, region7, region20,
								region21));
			}
			return result;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public String getTranamt(String str) {
		String tmp = "";
		char c[] = str.toCharArray();
		if (str.length() != 1) {
			for (int i = 0; i < c.length; i++) {
				// System.out.println(String.valueOf(c[i]).equals("0"));
				if (!String.valueOf(c[i + 1]).equals("0")) {
					// System.out.println("i " + i);
					tmp = str.substring(i + 1, str.length());
					// System.out.println(tmp);
					break;
				}
			}
		} else {
			tmp = str;
		}
		StringBuffer sb = new StringBuffer();
		sb.append(tmp);
		if (sb.length() > 2) {
			sb.insert(sb.length() - 2, ".");
		}
		if (sb.length() == 2) {
			sb.insert(0, "0.");
		}
		if (sb.length() == 1) {
			sb.insert(0, "0.0");
		}
		// System.out.println(sb.toString());
		return sb.toString();
	}
}
